
"""
A WHIFF resource for storing wwiki information.

Store wwiki data to a mysql database.

This implementation is kept simple, assuming the wwiki
will grow to no more than a few thousand entries.
"""

import MySQLdb

# sql to create the tables for the application

TABLE_CREATES = [
    """
    create table wwikientries (
        name varchar(100),
        title varchar(100),
        note varchar(200),
        summary varchar(200),
        timestamp char(20),
        the_text text
    )
    """,
    """
    create table wwikilogs (
        name varchar(100),
        title varchar(100),
        note varchar(200),
        summary varchar(200),
        timestamp char(20),
        the_text text
    )
    """,
    ]

# sql modification operations

INSERT_ENTRY = """
    insert into wwikientries
    (name, title, note, summary, timestamp, the_text)
    values (%s, %s, %s, %s, %s, %s)
"""

INSERT_LOG = """
    insert into wwikilogs
    (name, title, note, summary, timestamp, the_text)
    values (%s, %s, %s, %s, %s, %s)
"""

DELETE_ENTRY = """
    delete from wwikientries where name=%s
"""

# sql query operations

TEST_QUERY = "select * from wwikientries limit 1"

GET_ENTRIES = """
    select name, title, note, summary, timestamp, the_text
    from wwikilogs
    order by name
"""

class wikiStorage:
    
    def __init__(self, host, user, passwd, db):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.connection = None
        self.entriesDictionary = None
                
    def localize(self, env):
        "return a connection between an HTTP request and the storage resource"
        result = wikiStorage(self.host, self.user, self.passwd, self.db)
        # make sure the database is set up
        result.initTables()
        # get and cache entries
        result.getEntries()
        return result
    
    def get(self, pathlist):
        return self.entriesDictionary.copy()

    def put(self, pathlist, value):
        #pr "putting", value
        oldvalue = self.entriesDictionary
        #pr "old value is", oldvalue
        for (name, entry) in value.items():
            #pr "testing", (name, entry, oldvalue.get(name))
            if oldvalue.get(name)!=entry:
                #pr "adding", entry
                self.addEntry(entry)
        # refresh cached data from the database
        self.getEntries()

    def connect(self):
        "get cached connection to the database for the current request, or make a connection and cache it and return it"
        if self.connection is None:
            self.connection = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db)
        return self.connection

    def initTables(self):
        "make sure the wwiki tables exist, or create them if not"
        cursor = self.connect().cursor()
        try:
            cursor.execute(TEST_QUERY)
        except:
            for statement in TABLE_CREATES:
                cursor.execute(statement)

    def getEntries(self):
        cursor = self.connect().cursor()
        result = {}
        n = cursor.execute(GET_ENTRIES)
        #pr "got", n, "entries"
        for (name, title, note, summary, timestamp, text) in cursor.fetchall():
            D = {
                "Name": name,
                "Title": title,
                "Note": note,
                "Summary": summary,
                "TimeStamp": timestamp,
                "Text": text,
                }
            #pr "storing", D
            result[name] = D
        self.entriesDictionary = result
        #pr "final dictionary is", result
        return result
    
    def addEntry(self, entry):
        name = entry["Name"]
        title = entry["Title"]
        note = entry["Note"]
        summary = entry["Summary"]
        timestamp = entry["TimeStamp"]
        text = entry["Text"]
        conn = self.connect()
        cursor = conn.cursor()
        cursor.execute(DELETE_ENTRY, (name,))
        cursor.execute(INSERT_ENTRY, (name, title, note, summary, timestamp, text))
        cursor.execute(INSERT_LOG, (name, title, note, summary, timestamp, text))
        #pr "added entry", entry
        conn.commit()
